﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

namespace utd_hrrs
{
    public partial class Supervisor : System.Web.UI.Page
    {
        private string _strSQLconnection = "Data Source=rockstar.hopto.org,11433;Initial Catalog=UTD_HRRS;Persist Security Info=True;User ID=utd_hrrs;Password=bhowmik6360";
        private List<string> _overlappingReservations;

        protected void Page_Load(object sender, EventArgs e)
        {
            confirmTentativeButton.Click += new EventHandler(this.confirmTentativeButton_Click);
            deleteTentativeButton.Click += new EventHandler(this.deleteTentativeButton_Click);
            deleteConfirmedButton.Click += new EventHandler(this.deleteConfirmedButton_Click);
            maintenanceButton.Click += new EventHandler(this.maintenanceButton_Click);

            //switchViewCBox has AutoPostBack
            SwitchViewCBoxCheck();
            
            SqlConnection sqlConnection = new SqlConnection(_strSQLconnection);

            if (!IsPostBack == true)
            {
                SqlCommand sqlCommand = new SqlCommand("SELECT R.USER_ID, R.RESERV_ID, R.START_DATE, R.END_DATE, R.START_TIME, R.END_TIME, R.INV_ID, I.EQUIP_TYPE " +
                    "FROM [RESERVATION] AS R, [STATUS] AS S, [INVENTORY] AS I where R.STATUS_ID = S.STATUS_ID AND R.INV_ID = I.INV_ID AND S.RESERV_STATUS = 'n'", sqlConnection);
                GetAndBindSQLDataToGridView(sqlConnection, sqlCommand, tentativeReservGView);
                sqlCommand = new SqlCommand("SELECT R.USER_ID, R.RESERV_ID, R.START_DATE, R.END_DATE, R.START_TIME, R.END_TIME, R.INV_ID, I.EQUIP_TYPE " +
                    "FROM [RESERVATION] AS R, [STATUS] AS S, [INVENTORY] AS I where R.STATUS_ID = S.STATUS_ID AND R.INV_ID = I.INV_ID AND S.RESERV_STATUS = 'y'", sqlConnection);
                GetAndBindSQLDataToGridView(sqlConnection, sqlCommand, confirmedReservGView);
            }
        }

        private void GetAndBindSQLDataToGridView(SqlConnection sqlConnection, SqlCommand sqlCommand, GridView reservGView)
        {
            sqlConnection.Open();

            SqlDataReader reader = sqlCommand.ExecuteReader();

            reservGView.DataSource = reader;
            reservGView.DataBind();
            
            sqlConnection.Close();
            reader.Close();
            sqlCommand.Dispose();  
        } 

        private void SwitchViewCBoxCheck()
        {
            if (switchViewCBox.Checked == true && switchViewCBox.Text == "Switch to Confirmed View")
            {
                switchViewCBox.Text = "Switch to Tentative View";
                tentativePanel.Visible = false;
                confirmedPanel.Visible = true;
            }
            else if (switchViewCBox.Checked == true && switchViewCBox.Text == "Switch to Tentative View")
            {
                switchViewCBox.Text = "Switch to Confirmed View";
                confirmedPanel.Visible = false;
                tentativePanel.Visible = true;
            }
            
            switchViewCBox.Checked = false;
        }
        
        public void confirmTentativeButton_Click(Object sender, EventArgs e)
        {
            overlappingReservsLabel.Visible = false;
            overlappingReservsLabel.Text = "Due to overlapping reservations could not add: ";
            _overlappingReservations = new List<string>();

            //switchViewCBox.Text = "Switch to Tentative View2";
            //doesnt postback but still makes the changes
            //Response.Redirect("Supervisor.aspx");
            using (SqlConnection connection = new SqlConnection(_strSQLconnection))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction();

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    GridViewRowCollection gVRC = tentativeReservGView.Rows;
                    foreach (GridViewRow row in gVRC)
                    {
                        bool isChecked = ((CheckBox)row.FindControl("tentResvRowCBox")).Checked;
                        if (isChecked)
                        {
                            string reserv_ID = row.Cells[2].Text;
                            string start_Date = row.Cells[3].Text;
                            string end_Date = row.Cells[4].Text;
                            string start_Time = row.Cells[5].Text;
                            string end_Time = row.Cells[6].Text;
                            string inv_ID = row.Cells[7].Text;

                            SqlParameter param = new SqlParameter();
                            param.ParameterName = "@inv_ID";
                            param.Value = inv_ID;
                            command.Parameters.Add(param);

                            SqlParameter param1 = new SqlParameter();
                            param1.ParameterName = "@reserv_ID";
                            param1.Value = reserv_ID;
                            command.Parameters.Add(param1);

                            SqlParameter param2 = new SqlParameter();
                            param2.ParameterName = "@start_Date";
                            param2.Value = start_Date;
                            command.Parameters.Add(param2);

                            SqlParameter param3 = new SqlParameter();
                            param3.ParameterName = "@end_Date";
                            param3.Value = end_Date;
                            command.Parameters.Add(param3);

                            SqlParameter param4 = new SqlParameter();
                            param4.ParameterName = "@start_Time";
                            param4.Value = start_Time;
                            command.Parameters.Add(param4);

                            SqlParameter param5 = new SqlParameter();
                            param5.ParameterName = "@end_Time";
                            param5.Value = end_Time;
                            command.Parameters.Add(param5);

                            command.CommandText = "SELECT R.START_DATETIME, R.END_DATETIME FROM [RESERVATION] AS R WHERE R.RESERV_ID = @reserv_ID";

                            SqlDataReader reader0 = command.ExecuteReader();
                            reader0.Read();
                            DateTime startDateTime;
                            DateTime endDateTime;
                            startDateTime = reader0.GetDateTime(0);
                            endDateTime = reader0.GetDateTime(1);

                            reader0.Close();

                            SqlParameter param6 = new SqlParameter();
                            param6.ParameterName = "@start_DateTime";
                            param6.Value = startDateTime;
                            command.Parameters.Add(param6);

                            SqlParameter param7 = new SqlParameter();
                            param7.ParameterName = "@end_DateTime";
                            param7.Value = endDateTime;
                            command.Parameters.Add(param7);

                            command.CommandText = "SELECT * FROM [RESERVATION] AS R, [STATUS] AS S WHERE R.STATUS_ID = S.STATUS_ID "
                                + "AND S.RESERV_STATUS = 'y' AND R.INV_ID = @inv_ID AND (R.START_DATETIME <= @end_DateTime AND R.END_DATETIME >= @start_DateTime)";

                            SqlDataReader reader = command.ExecuteReader();

                            if (reader.HasRows == true)
                            {
                                _overlappingReservations.Add(reserv_ID);
                                reader.Close();
                                command.Parameters.Clear();
                            }
                            else
                            {
                                reader.Close();
                                command.Parameters.Clear();

                                param = new SqlParameter();
                                param.ParameterName = "@reserv_ID";
                                param.Value = reserv_ID;
                                command.Parameters.Add(param);

                                command.CommandText = "UPDATE R SET R.STATUS_ID = '2' FROM RESERVATION AS R WHERE R.RESERV_ID = @reserv_ID";
                                command.ExecuteNonQuery();
                            }
                            
                        }
                    }

                    // Attempt to commit the transaction.
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                    Console.WriteLine("  Message: {0}", ex.Message);

                    // Attempt to roll back the transaction.
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        // This catch block will handle any errors that may have occurred
                        // on the server that would cause the rollback to fail, such as
                        // a closed connection.
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("  Message: {0}", ex2.Message);
                    }
                }
            }

            foreach (string reservID in _overlappingReservations)
            {
                overlappingReservsLabel.Text += reservID + ", ";
            }

            if (_overlappingReservations.Count > 0)
            {
                overlappingReservsLabel.Text =
                    overlappingReservsLabel.Text.Substring(0, overlappingReservsLabel.Text.Length - 2);
                overlappingReservsLabel.Visible = true;
            }

            RefreshGridViews();
            //Response.Redirect("Supervisor.aspx");
        }
        
        public void deleteTentativeButton_Click(Object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection(_strSQLconnection);
            string deleteStatementStr = "DELETE FROM [RESERVATION] WHERE ";

            GridViewRowCollection gVRC = tentativeReservGView.Rows;
            foreach(GridViewRow row in gVRC)
            {
                bool isChecked = ((CheckBox)row.FindControl("tentResvRowCBox")).Checked;
                if (isChecked)
                {
                    string reserv_ID = row.Cells[2].Text;
                    deleteStatementStr = deleteStatementStr + "RESERV_ID " + "=" + " " + "'" + reserv_ID + "'" + " OR ";
                }                            
            }

            if (deleteStatementStr.Length > 32)
            {
                deleteStatementStr = deleteStatementStr.Substring(0, deleteStatementStr.Length - 4);

                sqlConnection.Open();
                SqlCommand cmd = new SqlCommand(deleteStatementStr, sqlConnection);
                cmd.ExecuteNonQuery();
                sqlConnection.Close();

                Response.Redirect("Supervisor.aspx");
            }    
        }

        public void deleteConfirmedButton_Click(Object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection(_strSQLconnection);
            string deleteStatementStr = "DELETE FROM [RESERVATION] WHERE ";

            GridViewRowCollection gVRC = confirmedReservGView.Rows;
            foreach (GridViewRow row in gVRC)
            {
                bool isChecked = ((CheckBox)row.FindControl("confResvRowCBox")).Checked;
                if (isChecked)
                {
                    string reserv_ID = row.Cells[2].Text;
                    deleteStatementStr = deleteStatementStr + "RESERV_ID " + "=" + " " + "'" + reserv_ID + "'" + " OR ";
                }
            }

            if (deleteStatementStr.Length > 32)
            {
                deleteStatementStr = deleteStatementStr.Substring(0, deleteStatementStr.Length - 4);

                sqlConnection.Open();
                SqlCommand cmd = new SqlCommand(deleteStatementStr, sqlConnection);
                cmd.ExecuteNonQuery();
                sqlConnection.Close();

                Response.Redirect("Supervisor.aspx");
            }
        }

        public void maintenanceButton_Click(Object sender, EventArgs e)
        {
            Response.Redirect("SupervisorPage.aspx");
        }

        public void RefreshGridViews()
        {
            SqlConnection sqlConnection = new SqlConnection(_strSQLconnection);

            SqlCommand sqlCommand = new SqlCommand("SELECT R.USER_ID, R.RESERV_ID, R.START_DATE, R.END_DATE, R.START_TIME, R.END_TIME, R.INV_ID, I.EQUIP_TYPE " +
                "FROM [RESERVATION] AS R, [STATUS] AS S, [INVENTORY] AS I where R.STATUS_ID = S.STATUS_ID AND R.INV_ID = I.INV_ID AND S.RESERV_STATUS = 'n'", sqlConnection);
            GetAndBindSQLDataToGridView(sqlConnection, sqlCommand, tentativeReservGView);

            sqlCommand = new SqlCommand("SELECT R.USER_ID, R.RESERV_ID, R.START_DATE, R.END_DATE, R.START_TIME, R.END_TIME, R.INV_ID, I.EQUIP_TYPE " +
                "FROM [RESERVATION] AS R, [STATUS] AS S, [INVENTORY] AS I where R.STATUS_ID = S.STATUS_ID AND R.INV_ID = I.INV_ID AND S.RESERV_STATUS = 'y'", sqlConnection);
            GetAndBindSQLDataToGridView(sqlConnection, sqlCommand, confirmedReservGView);
        }

    }
}